//
//  YWKeepWatchDBManager.m
//  maintain
//
//  Created by Kyo on 16/9/1.
//  Copyright © 2016年 ewin. All rights reserved.
//

#import "YWKeepWatchDBManager.h"
#import "YWDBTool.h"
#import "YWCommonTool.h"
#import "YWLoginTool.h"
#import "YWLoginUser.h"

#import "YWFloor.h"
#import "YWApartment.h"
#import "YWBuilding.h"
#import "YWReply.h"
#import "YWReplyDBManager.h"
#import "YWKeepWatchMission.h"

@implementation YWKeepWatchDBManager
YWSingletonM(Manager)

- (FMDatabase*)fmdb{
    return [[YWDBTool shareDBTool] fmdb];
}
- (FMDatabaseQueue*)dbQeue{
    return [[YWDBTool shareDBTool] dbQeue];
}

- (void)createAllKeepWatchTables{
    [self createKeepWatchMissionTable];
    [self createPatrolLineTable];
    [self createPatrolLineWeekRelTable];
    [self createPatrolLineLocationRelTable];
    
    [self createKeepWatchMissionExecutorRelTable];
    [self createPatrollineRecordTable];
    [self createPatrolPointRecordTable];
}

- (void)dropAndReCreateKeepWatchTables{
    [self dropAndReCreateKeepWatchMissionTable];
    //3.2.1
    [self dropAndReCreatePatrollineRecordTable];
    [self dropAndReCreatePatrolPointRecordTable];
}

#pragma mark - 巡更任务表 keepwatchmission
NSString *const KSqlCreateKeepWatchMissionTable = @"CREATE TABLE IF NOT EXISTS 'keepwatchmission' (keepWatchMissionId integer NOT NULL,patrolLineId integer,createTime timestamp,status smallint,startDate timestamp,endDate timestamp,startTime varchar(10),endTime varchar(10),creatorId integer,missionSequence text,buildingId varchar,observerIds varchar);";

- (BOOL)createKeepWatchMissionTable{
    return [[self fmdb] executeUpdate:KSqlCreateKeepWatchMissionTable];
}
- (void)dropAndReCreateKeepWatchMissionTable{
    [self.dbQeue inDatabase:^(FMDatabase* db) {
        NSString* sql = @"drop table if exists keepwatchmission;";
        if ([db executeUpdate:sql]) {
            [db executeUpdate:KSqlCreateKeepWatchMissionTable];
        }
    }];
}
#pragma mark - 巡更路线表 patrolline
NSString *const KSqlCreatePatrolLineTable = @"CREATE TABLE IF NOT EXISTS 'patrolline' (patrolLineId integer NOT NULL,patrolLineName varchar,intervalTime integer,creatorId integer,creatorName varchar,createTime timestamp,updateTime timestamp,note text);";

- (BOOL)createPatrolLineTable{
    return [[self fmdb] executeUpdate:KSqlCreatePatrolLineTable];
}

- (void)dropAndReCreatePatrolLineTable{
    [self.dbQeue inDatabase:^(FMDatabase* db) {
        NSString* sql = @"drop table if exists patrolline;";
        if ([db executeUpdate:sql]) {
            [db executeUpdate:KSqlCreatePatrolLineTable];
        }
    }];
}
#pragma mark - 巡更路线每周循环天表 patrolline_week_rel
- (BOOL)createPatrolLineWeekRelTable{
    NSString* sql = @"CREATE TABLE IF NOT EXISTS 'patrolline_week_rel' (patrolLineId integer NOT NULL,week integer);";
    return [[self fmdb] executeUpdate:sql];
}
#pragma mark - 巡更路线巡更点表 patrolline_location_rel
- (BOOL)createPatrolLineLocationRelTable
{
    NSString* sql = @"CREATE TABLE IF NOT EXISTS 'patrolline_location_rel' (patrolLineId integer,sort integer,locationId integer,note text);";
    return [[self fmdb] executeUpdate:sql];
}

#pragma mark - 巡更任务与巡更执行人表 keepwatchmission_executor_rel
- (BOOL)createKeepWatchMissionExecutorRelTable
{
    NSString* sql = @"CREATE TABLE IF NOT EXISTS 'keepwatchmission_executor_rel' (uniqueId integer NOT NULL,userName varchar(200),mobile varchar(200),headIcon varchar(200),keepWatchMissionId integer NOT NULL);";
    return [[self fmdb] executeUpdate:sql];
}
#pragma mark - 巡更路线记录表 patrolline_record
NSString *const KSqlCreatePatrollineRecordTable = @"CREATE TABLE IF NOT EXISTS 'patrolline_record' (patrolLineId integer, uniqueTag varchar,startTime timestamp,endTime timestamp,status smallint,count integer,result integer,keepWatchMissionId integer ,recordId integer,isRead boolean DEFAULT 0,recordSequence varchar,executorIds varchar);";

- (BOOL)createPatrollineRecordTable
{
    return [[self fmdb] executeUpdate:KSqlCreatePatrollineRecordTable];
}
- (void)dropAndReCreatePatrollineRecordTable{
    [self.dbQeue inDatabase:^(FMDatabase* db) {
        NSString* sql = @"drop table if exists patrolline_record;";
        if ([db executeUpdate:sql]) {
            [db executeUpdate:KSqlCreatePatrollineRecordTable];
        }
    }];
}
#pragma mark - 巡更点记录表 patrolpoint_record
NSString *const KSqlCreatePatrolPointRecordTable = @"CREATE TABLE IF NOT EXISTS 'patrolpoint_record' (patrolpointRecordId varchar,uniqueTag varchar,status smallint,locationId integer, patrolTime timestamp ,recordNote varchar(200),sort integer,uploadStatus integer,patrolLineId integer,keepWatchMissionId integer,executorId integer,detailUniqueTag varchar);";

- (BOOL)createPatrolPointRecordTable
{
    return [[self fmdb] executeUpdate:KSqlCreatePatrolPointRecordTable];
}

- (void)dropAndReCreatePatrolPointRecordTable{
    [self.dbQeue inDatabase:^(FMDatabase* db) {
        NSString* sql = @"drop table if exists patrolpoint_record;";
        if ([db executeUpdate:sql]) {
            [db executeUpdate:KSqlCreatePatrolPointRecordTable];
        }
    }];
}

- (BOOL)updateKeepWatchMissionWithArray:(NSArray*)keepWatchMissions
{
    if (keepWatchMissions.count==0) {
        return YES;
    }
    __block BOOL executeResult = YES;
    __block NSArray* blockMissions = keepWatchMissions;
    __block NSString* sql = nil;
    [self.dbQeue inTransaction:^(FMDatabase* db, BOOL* rollback) {
        [blockMissions enumerateObjectsUsingBlock:^(YWKeepWatchMission* _Nonnull keepWatchMission, NSUInteger idx, BOOL* _Nonnull stop) {
            sql = @"SELECT  * FROM 'keepwatchmission' WHERE keepWatchMissionId=?";
            FMResultSet* set = [db executeQuery:sql, @(keepWatchMission.keepWatchMissionId)];
            if ([set next]) {
                if (keepWatchMission.status != -2) {
                    sql = @"UPDATE 'keepwatchmission' SET patrolLineId=?,createTime= ?,status= ? ,startDate= ?, endDate = ?,startTime = ? ,endTime = ?, creatorId = ?,missionSequence=?,buildingId=?,observerIds=? WHERE keepWatchMissionId = ?;";//observerIds不在任务接口更新，会在详情页单独更新
                    executeResult = [db executeUpdate:sql, @(keepWatchMission.patrolLine.patrolLineId),@(keepWatchMission.createTime), @(keepWatchMission.status), @(keepWatchMission.startDate), @(keepWatchMission.endDate), keepWatchMission.startTime, keepWatchMission.endTime,@(keepWatchMission.creator.uniqueId),keepWatchMission.missionSequence,keepWatchMission.buildingId,keepWatchMission.observerIds,@(keepWatchMission.keepWatchMissionId)];
                }
            }
            else {
                sql = @"INSERT INTO 'keepwatchmission' (keepWatchMissionId,patrolLineId,createTime,status,startDate,endDate,startTime,endTime,creatorId,missionSequence,buildingId,observerIds) VALUES (?, ?, ?, ?, ?,?,?,?,?,?,?,?);";
                executeResult = [db executeUpdate:sql, @(keepWatchMission.keepWatchMissionId), @(keepWatchMission.patrolLine.patrolLineId),@(keepWatchMission.createTime), @(keepWatchMission.status), @(keepWatchMission.startDate), @(keepWatchMission.endDate), keepWatchMission.startTime, keepWatchMission.endTime,@(keepWatchMission.creator.uniqueId),keepWatchMission.missionSequence,keepWatchMission.buildingId,keepWatchMission.observerIds];
            }
            //sql = @"DELETE FROM 'reply' WHERE relationId=?";
            //[db executeUpdate:sql, @(keepWatchMission.keepWatchMissionId)];
            if (!executeResult) {
                *stop = YES;
                *rollback = YES;
            };
        }];
        
        [db closeOpenResultSets];
    }];
//    __block NSMutableArray* locationsArray = [NSMutableArray array];
//    __block NSMutableArray* floorsArray = [NSMutableArray array];
//    __block NSMutableArray* apartmentsArray = [NSMutableArray array];
//    __block NSMutableArray* buildingsArray = [NSMutableArray array];
//    __block NSMutableArray* replysArray = [NSMutableArray array];
    [blockMissions enumerateObjectsUsingBlock:^(YWKeepWatchMission* _Nonnull keepWatchMission, NSUInteger idx, BOOL* _Nonnull stop) {
        //评论
//        [keepWatchMission.replies enumerateObjectsUsingBlock:^(YWReply*  _Nonnull reply, NSUInteger idx, BOOL * _Nonnull stop) {
//            reply.relationId = keepWatchMission.keepWatchMissionId;
//            reply.relationType = YWReplyRelationTypeKeepWatch;
//        }];
//        [replysArray addObjectsFromArray:keepWatchMission.replies];
        //线路
        [self updatePatrolLineIdAndName:keepWatchMission.patrolLine];
        //执行人
        [self updateKeepWatchMissionExecutor:keepWatchMission.executors keepWatchMissionId:keepWatchMission.keepWatchMissionId];
//        [keepWatchMission.patrolLine.locationRels enumerateObjectsUsingBlock:^(YWPatrolLineLocationRel* _Nonnull locationRel, NSUInteger idx, BOOL* _Nonnull stop) {
//            YWLocation* location = locationRel.location;
//            if (location) {
//                [locationsArray addObject:location];
//                YWFloor* floor = locationRel.location.floor;
//                if (floor) {
//                    [floorsArray addObject:floor];
//                    YWApartment* apartment = locationRel.location.floor.apartment;
//                    if (apartment) {
//                        [apartmentsArray addObject:apartment];
//                        YWBuilding* building = locationRel.location.floor.apartment.building;
//                        if (building) {
//                            [buildingsArray addObject:building];
//                        }
//                    }
//                }
//            }
//        }];
    }];
//    [[YWDBTool shareDBTool] updateLocationWithArray:locationsArray];
//    [[YWDBTool shareDBTool] updateFloorWithArray:floorsArray];
//    [[YWDBTool shareDBTool] updateApartmentWithArray:apartmentsArray];
//    [[YWDBTool shareDBTool] updateBuildingWithArray:buildingsArray];
//    //更新评论
//    [[YWReplyDBManager shareManager] updateReplyWithReplyArray:replysArray];
    return executeResult;
}

- (BOOL)updateKeepWatchDeleteStatusWithMissionId:(NSInteger)keepWatchMissionId{
    __block BOOL executeResult = YES;
    [self.dbQeue inDatabase:^(FMDatabase* db) {
        NSString* sql = @"UPDATE 'keepwatchmission' SET status=? WHERE keepWatchMissionId=?;";
        executeResult = [db executeUpdate:sql, @(-1),@(keepWatchMissionId)];
        [db closeOpenResultSets];
    }];
    return executeResult;
}

- (BOOL)updatePatrolLineIdAndName:(YWPatrolLine *)patrolLine{
    if (patrolLine.patrolLineId<=0) {
        return YES;
    }
    __block YWPatrolLine* blockPatrolLine = patrolLine;
    __block BOOL executeResult = YES;
    [self.dbQeue inTransaction:^(FMDatabase* db, BOOL* rollback) {
        NSString* sql = @"SELECT  * FROM 'patrolline' WHERE patrolLineId = ?;";
        FMResultSet* set = [db executeQuery:sql,@(patrolLine.patrolLineId)];
        if ([set next]) {
            sql = @"UPDATE 'patrolline' SET patrolLineName=? WHERE patrolLineId = ?";
            executeResult = [db executeUpdate:sql, blockPatrolLine.patrolLineName,@(blockPatrolLine.patrolLineId)];
        }else {
            sql = @"INSERT INTO 'patrolline' (patrolLineId,patrolLineName) VALUES (?,?);";
            executeResult = [db executeUpdate:sql, @(blockPatrolLine.patrolLineId), blockPatrolLine.patrolLineName];
        }
        if (!executeResult) {
            *rollback = YES;
        }
        [db closeOpenResultSets];
    }];
    //星期
    [self updatePatrollineWeek:patrolLine.weekRels patrolLineId:patrolLine.patrolLineId];
    //位置
    [self updatePatrollineLocation:patrolLine.locationRels patrolLineId:patrolLine.patrolLineId];
    return executeResult;
}

- (BOOL)updatePatrolLineWithLine:(YWPatrolLine *)patrolLine
{
    if (patrolLine.patrolLineId<=0 || patrolLine.intervalMinute<=0) {
        return YES;
    }
    __block YWPatrolLine* blockPatrolLine = patrolLine;
    __block BOOL executeResult = YES;
    [self.dbQeue inTransaction:^(FMDatabase* db, BOOL* rollback) {
        __block NSString* sql = nil;
        sql = @"SELECT  * FROM 'patrolline' WHERE patrolLineId=?";
        FMResultSet* set = [db executeQuery:sql,@(patrolLine.patrolLineId)];
        if ([set next]) {
            sql = @"UPDATE 'patrolline' SET patrolLineName=?,intervalTime=?,creatorId=?,creatorName=?,createTime=?,updateTime=?,note=? WHERE patrolLineId = ?;";
            executeResult = [db executeUpdate:sql, blockPatrolLine.patrolLineName, @(blockPatrolLine.intervalMinute), @(blockPatrolLine.creator.uniqueId), blockPatrolLine.creator.userName, @(blockPatrolLine.createTime),@(blockPatrolLine.updateTime),blockPatrolLine.note,@(blockPatrolLine.patrolLineId)];
        }
        else {
            sql = @"INSERT INTO 'patrolline' (patrolLineId,patrolLineName,intervalTime,creatorId,creatorName,createTime,updateTime,note) VALUES (?,?,?,?,?,?,?,?);";
            executeResult = [db executeUpdate:sql, @(blockPatrolLine.patrolLineId), blockPatrolLine.patrolLineName, @(blockPatrolLine.intervalMinute), @(blockPatrolLine.creator.uniqueId), blockPatrolLine.creator.userName, @(blockPatrolLine.createTime),@(blockPatrolLine.updateTime), blockPatrolLine.note];
        }
        if (!executeResult) {
            *rollback = YES;
        }
        [db closeOpenResultSets];
    }];
    //星期
    [self updatePatrollineWeek:patrolLine.weekRels patrolLineId:patrolLine.patrolLineId];
    //位置
    [self updatePatrollineLocation:patrolLine.locationRels patrolLineId:patrolLine.patrolLineId];
    return executeResult;
}

- (NSArray *)needRequestPatrolLines{
    __block NSMutableArray* lineArray = [NSMutableArray array];
    [self.dbQeue inDatabase:^(FMDatabase* db) {
        NSString* sql = @"SELECT patrolLineId,updateTime FROM 'patrolline' WHERE patrolLineId in (select distinct patrolLineId FROM 'keepwatchmission' where keepWatchMissionId>0 AND patrolLineId>0 AND status=0)";
        FMResultSet* set = [db executeQuery:sql];
        while ([set next]) {
            YWPatrolLine *patrolLine = [[YWPatrolLine alloc] init];
            patrolLine.patrolLineId = [set intForColumn:@"patrolLineId"];
            patrolLine.updateTime = [set longLongIntForColumn:@"updateTime"];
            [lineArray addObject:patrolLine];
        }
        [db closeOpenResultSets];
    }];
    return lineArray;
}

- (BOOL)updatePatrolLine:(NSArray *)patrolLineArray{
    if (patrolLineArray.count==0) {
        return YES;
    }
    __block BOOL executeResult = YES;
    [patrolLineArray enumerateObjectsUsingBlock:^(YWPatrolLine*  _Nonnull patrolLine, NSUInteger idx, BOOL * _Nonnull stop) {
        [self.dbQeue inTransaction:^(FMDatabase* db, BOOL* rollback) {
            NSString* sql = nil;
            sql = @"SELECT  * FROM 'patrolline' WHERE patrolLineId=?";
            FMResultSet* set = [db executeQuery:sql,@(patrolLine.patrolLineId)];
            if ([set next]) {
                if (patrolLine.intervalMinute>0) {
                    sql = @"UPDATE 'patrolline' SET patrolLineName=?,intervalTime=?,creatorId=?,creatorName=?,createTime=?,updateTime=?,note=? WHERE patrolLineId = ?;";
                    executeResult = [db executeUpdate:sql, patrolLine.patrolLineName, @(patrolLine.intervalMinute), @(patrolLine.creator.uniqueId), patrolLine.creator.userName, @(patrolLine.createTime),@(patrolLine.updateTime),patrolLine.note,@(patrolLine.patrolLineId)];
                }
            }
            if (!executeResult) {
                *stop = YES;
                *rollback = YES;}
            [db closeOpenResultSets];
        }];
    }];
    [patrolLineArray enumerateObjectsUsingBlock:^(YWPatrolLine * _Nonnull patrolLine, NSUInteger idx, BOOL * _Nonnull stop) {
        //星期
        [self updatePatrollineWeek:patrolLine.weekRels patrolLineId:patrolLine.patrolLineId];
        //位置
        [self updatePatrollineLocation:patrolLine.locationRels patrolLineId:patrolLine.patrolLineId];
    }];
    return executeResult;
}

- (BOOL)updateKeepWatchMissionExecutor:(NSArray*)executors keepWatchMissionId:(NSInteger)keepWatchMissionId
{
    if (executors.count==0 || keepWatchMissionId<=0) {
        return YES;
    }
    __block NSArray* blockExecutors = executors;
    __block BOOL executeResult = YES;
    [self.dbQeue inTransaction:^(FMDatabase* db, BOOL* rollback) {
        __block NSString* sql = nil;
        sql = @"delete FROM 'keepwatchmission_executor_rel' WHERE keepWatchMissionId=?;";
        executeResult = [db executeUpdate:sql, @(keepWatchMissionId)];
        if (!executeResult) {
            *rollback = YES;
        }
        [blockExecutors enumerateObjectsUsingBlock:^(YWHandler* _Nonnull handler, NSUInteger idx, BOOL* _Nonnull stop) {
            sql = @"INSERT INTO 'keepwatchmission_executor_rel' (uniqueId,userName,mobile ,headIcon ,keepWatchMissionId) VALUES (?, ?, ?, ?, ?);";
            BOOL result = [db executeUpdate:sql, @(handler.uniqueId), handler.userName, handler.mobile, handler.headIcon, @(keepWatchMissionId)];
            if (!result) {
                *stop = YES;
                *rollback = YES;
                executeResult = NO;
            };
        }];
        [db closeOpenResultSets];
    }];
    //保存巡更人到handler
    //[[YWDBTool shareDBTool] updateHandlerWithArray:blockExecutors];
    return executeResult;
}
- (BOOL)updatePatrollineWeek:(NSArray*)patrolLineWeekRels patrolLineId:(NSInteger)patrolLineId
{
    if (patrolLineId<=0 || patrolLineWeekRels.count==0) {
        return YES;
    }
    __block NSArray* blockPatrolLineWeekRels = patrolLineWeekRels;
    __block BOOL executeResult = YES;
    [self.dbQeue inTransaction:^(FMDatabase* db, BOOL* rollback) {
        __block NSString* sql = nil;
        sql = @"delete FROM 'patrolline_week_rel' WHERE patrolLineId=?;";
        executeResult = [db executeUpdate:sql, @(patrolLineId)];
        if (!executeResult) {
            *rollback = YES;
        }
        [blockPatrolLineWeekRels enumerateObjectsUsingBlock:^(YWPatrolLineWeekRel* _Nonnull patrolLineWeekRel, NSUInteger idx, BOOL* _Nonnull stop) {
            sql = @"INSERT INTO 'patrolline_week_rel' (patrolLineId ,week) VALUES (?, ?);";
            BOOL result = [db executeUpdate:sql, @(patrolLineWeekRel.patrolLineId), @(patrolLineWeekRel.week)];
            if (!result) {
                *stop = YES;
                *rollback = YES;
                executeResult = NO;
            };
        }];
        [db closeOpenResultSets];
    }];
    return executeResult;
}
- (BOOL)updatePatrollineLocation:(NSArray*)patrolLineLocationRels patrolLineId:(NSInteger)patrolLineId
{
    if (patrolLineId<=0 || patrolLineLocationRels.count==0) {
        return YES;
    }
    __block NSArray* blockPatrolLineLocationRels = patrolLineLocationRels;
    __block BOOL executeResult = YES;
    [self.dbQeue inTransaction:^(FMDatabase* db, BOOL* rollback) {
        __block NSString* sql = nil;
        sql = @"delete FROM 'patrolline_location_rel' WHERE patrolLineId=?;";
        executeResult = [db executeUpdate:sql, @(patrolLineId)];
        if (!executeResult) {
            *rollback = YES;
        }
        [blockPatrolLineLocationRels enumerateObjectsUsingBlock:^(YWPatrolLineLocationRel* _Nonnull patrolLineLocationRel, NSUInteger idx, BOOL* _Nonnull stop) {
            sql = @"INSERT INTO 'patrolline_location_rel' (patrolLineId ,sort,locationId,note) VALUES (?, ?, ?, ?);";
            BOOL result = [db executeUpdate:sql, @(patrolLineLocationRel.patrolLineId), @(patrolLineLocationRel.sort), @(patrolLineLocationRel.locationId), patrolLineLocationRel.note];
            if (!result) {
                *stop = YES;
                *rollback = YES;
                executeResult = NO;
            };
        }];
        [db closeOpenResultSets];
    }];
    return executeResult;
}
- (NSArray*)allKeepWatchMissions{
    __block NSMutableArray* missions = [NSMutableArray array];
    [self.dbQeue inDatabase:^(FMDatabase* db) {
        YWLoginUser *currentUser = [YWLoginTool loginUser];
        NSTimeInterval currentTimeInterval = [[NSDate date] timeIntervalSince1970]*1000;
        NSString* sql = [NSString stringWithFormat:@"SELECT * FROM 'keepwatchmission' WHERE (creatorId=%ld or (keepWatchMissionId in (SELECT keepWatchMissionId FROM 'keepwatchmission_executor_rel' WHERE uniqueId = %ld) or (observerIds like '%%%ld%%'))) AND status = 0 AND endDate>%f ORDER BY createTime DESC",(long)currentUser.uniqueId,(long)currentUser.uniqueId,(long)currentUser.uniqueId,currentTimeInterval];
        FMResultSet* set = [db executeQuery:sql];
        while ([set next]) {
            YWKeepWatchMission* keepWatchMission = [self keepWatchMissionWithFMResultSet:set FMDatabase:db];
            [missions addObject:keepWatchMission];
        }
        [db closeOpenResultSets];
    }];
    return missions;
}

- (NSArray *)keepWatchMissionsWithPage:(NSInteger)page{
    __block NSMutableArray* missions = [NSMutableArray array];
    NSInteger showCount = page * 10;
    [self.dbQeue inDatabase:^(FMDatabase* db) {
        YWLoginUser *currentUser = [YWLoginTool loginUser];
        NSTimeInterval currentTimeInterval = [[NSDate date] timeIntervalSince1970]*1000;
        NSString* sql = [NSString stringWithFormat:@"SELECT * FROM 'keepwatchmission' WHERE (creatorId=%ld or (keepWatchMissionId in (SELECT keepWatchMissionId FROM 'keepwatchmission_executor_rel' WHERE uniqueId = %ld) or (observerIds like '%%%ld%%'))) AND status = 0 AND endDate>%f ORDER BY createTime DESC LIMIT %ld",(long)currentUser.uniqueId,(long)currentUser.uniqueId,(long)currentUser.uniqueId,currentTimeInterval,(long)showCount];
        FMResultSet* set = [db executeQuery:sql];
        while ([set next]) {
            YWKeepWatchMission *keepWatchMission = [self keepWatchMissionWithFMResultSet:set FMDatabase:db];
            //获取当前巡更区段
            keepWatchMission.currentLooperTimeArray = [YWCommonTool looperTimeArrayWithMission:keepWatchMission];
            [missions addObject:keepWatchMission];
        }
        [db closeOpenResultSets];
    }];
    return missions.count ? missions : nil;
}

- (NSString *)allKeepWatchMissionIds{
    NSMutableString *ids = [NSMutableString string];
    [self.dbQeue inDatabase:^(FMDatabase* db) {
        NSString* sql = @"SELECT keepWatchMissionId FROM 'keepwatchmission' WHERE status = 0";
        FMResultSet* set = [db executeQuery:sql];
        while ([set next]) {
            NSInteger keepWatchMissionId = [set intForColumn:@"keepWatchMissionId"];
            if (keepWatchMissionId>0) {
                [ids appendString:[@(keepWatchMissionId) stringValue]];
                [ids appendString:@","];
            }
        }
        [db closeOpenResultSets];
    }];
    return [NSString stringWithFormat:@"%@",ids];
}
- (YWKeepWatchMission*)keepWatchMissionWithMissionId:(NSInteger)keepWatchMissionId
{
    __block YWKeepWatchMission* keepWatchMission = nil;
    [self.dbQeue inDatabase:^(FMDatabase* db) {
        NSString* sql = @"SELECT * FROM 'keepwatchmission' WHERE keepWatchMissionId = ?";
        FMResultSet* set = [db executeQuery:sql, @(keepWatchMissionId)];
        if ([set next]) {
            keepWatchMission = [self keepWatchMissionWithFMResultSet:set FMDatabase:db];
        }
        [db closeOpenResultSets];
    }];
    return keepWatchMission;
}
- (YWKeepWatchMission*)keepWatchMissionWithFMResultSet:(FMResultSet*)set FMDatabase:(FMDatabase*)db
{
    YWKeepWatchMission* mission = [[YWKeepWatchMission alloc] init];
    mission.keepWatchMissionId = [set intForColumn:@"keepWatchMissionId"];
    mission.buildingId = [set stringForColumn:@"buildingId"];
    mission.createTime = [set longLongIntForColumn:@"createTime"];
    mission.status = [set intForColumn:@"status"];
    mission.startDate = [set longLongIntForColumn:@"startDate"];
    mission.endDate = [set longLongIntForColumn:@"endDate"];
    mission.startTime = [set stringForColumn:@"startTime"];
    mission.endTime = [set stringForColumn:@"endTime"];
    mission.missionSequence = [set stringForColumn:@"missionSequence"];
    
    mission.observerIds = [set stringForColumn:@"observerIds"];
    NSMutableArray *observer = [NSMutableArray array];
    NSString *observerSql = [NSString stringWithFormat:@"SELECT * FROM 'handler' WHERE uniqueId in (%@)",mission.observerIds];
    FMResultSet *observerSet = [db executeQuery:observerSql];
    while ([observerSet next]) {
        YWHandler *handler = [[YWDBTool shareDBTool] handlerWithSet:observerSet];
        [observer addObject:handler];
    }
    mission.observers = observer;
    
    NSInteger creatorId = [set intForColumn:@"creatorId"];
    NSString* handlersql = @"SELECT * FROM 'handler' WHERE uniqueId = ?;";
    FMResultSet* handlerSet = [db executeQuery:handlersql, @(creatorId)];
    if ([handlerSet next]) {
        mission.creator = [[YWDBTool shareDBTool] handlerWithSet:handlerSet];
    }
    NSInteger patrollineId = [set intForColumn:@"patrolLineId"];
    NSString* sql = @"SELECT * FROM 'patrolline' WHERE patrolLineId=?";
    FMResultSet* newSet = [db executeQuery:sql, @(patrollineId)];
    YWPatrolLine* patrolLine = nil;
    if ([newSet next]) {
        patrolLine = [self patrolLineWithFMResultSet:newSet FMDatabase:db];
    }
    mission.patrolLine = patrolLine;
    
    NSMutableArray* executors = [NSMutableArray array];
    sql = @"SELECT * FROM 'keepwatchmission_executor_rel' WHERE keepWatchMissionId = ?;";
    newSet = [db executeQuery:sql, @(mission.keepWatchMissionId)];
    while ([newSet next]) {
        NSInteger executorId = [newSet intForColumn:@"uniqueId"];
        NSString* executorSql = @"SELECT * FROM 'handler' WHERE uniqueId = ?;";
        FMResultSet* executorSet = [db executeQuery:executorSql, @(executorId)];
        if ([executorSet next]) {
            YWHandler *executor = [[YWDBTool shareDBTool] handlerWithSet:executorSet];
            [executors addObject:executor];
        }
    }
    mission.executors = executors;
    
    return mission;
}

- (YWPatrolLine*)patrolLineWithFMResultSet:(FMResultSet*)set FMDatabase:(FMDatabase*)db{
    YWPatrolLine *patrolLine = [[YWPatrolLine alloc] init];
    patrolLine.patrolLineId = [set intForColumn:@"patrolLineId"];
    patrolLine.patrolLineName = [set stringForColumn:@"patrolLineName"];
    patrolLine.intervalMinute = [set intForColumn:@"intervalTime"];
    YWHandler* creater = [[YWHandler alloc] init];
    creater.uniqueId = [set intForColumn:@"creatorId"];
    creater.userName = [set stringForColumn:@"creatorName"];
    patrolLine.creator = creater;
    patrolLine.createTime = [set longLongIntForColumn:@"createTime"];
    patrolLine.updateTime = [set longLongIntForColumn:@"updateTime"];
    patrolLine.note = [set stringForColumn:@"note"];
    
    NSMutableArray* locationRels = [NSMutableArray array];
    NSString *sql = @"SELECT * FROM 'patrolline_location_rel' WHERE patrolLineId = ?;";
    FMResultSet *newSet = [db executeQuery:sql, @(patrolLine.patrolLineId)];
    while ([newSet next]) {
        YWPatrolLineLocationRel* locationRel = [[YWPatrolLineLocationRel alloc] init];
        locationRel.patrolLineId = [newSet intForColumn:@"patrolLineId"];
        locationRel.sort = [newSet intForColumn:@"sort"];
        locationRel.locationId = [newSet intForColumn:@"locationId"];
        locationRel.note = [newSet stringForColumn:@"note"];
        NSString* locationSql = @"SELECT * FROM 'location' WHERE locationId = ?;";
        FMResultSet* locationSet = [db executeQuery:locationSql, @(locationRel.locationId)];
        if ([locationSet next]) {
            locationRel.location = [[YWDBTool shareDBTool] locationWithFMResultSet:locationSet];
        }
        [locationRels addObject:locationRel];
    }
    patrolLine.locationRels = locationRels;
    
    
    NSMutableArray* weekRels = [NSMutableArray array];
    sql = @"SELECT * FROM 'patrolline_week_rel' WHERE patrolLineId = ? ORDER BY week;";
    newSet = [db executeQuery:sql, @(patrolLine.patrolLineId)];
    while ([newSet next]) {
        YWPatrolLineWeekRel* weekRel = [[YWPatrolLineWeekRel alloc] init];
        weekRel.patrolLineId = [newSet intForColumn:@"patrolLineId"];
        weekRel.week = [newSet intForColumn:@"week"];
        [weekRels addObject:weekRel];
    }
    patrolLine.weekRels = weekRels;
    
    return patrolLine;
}
////该方法是本地巡更时更新数据用，下面是从网络获取数据更新数据库方法
- (BOOL)updatePatrollineRecordWithArray:(NSArray*)patrollineRecords
{
    __block BOOL executeResult = YES;
    __block NSArray* blockPatrollineRecords = patrollineRecords;
    __block NSString* sql = nil;
    [self.dbQeue inTransaction:^(FMDatabase* db, BOOL* rollback) {
        [blockPatrollineRecords enumerateObjectsUsingBlock:^(YWPatrollineRecord* _Nonnull patrollineRecord, NSUInteger idx, BOOL* _Nonnull stop) {
            sql = [NSString stringWithFormat:@"SELECT  * FROM 'patrolline_record' WHERE patrolLineId=%ld AND uniqueTag like '%%%@%%'",(long)patrollineRecord.patrolLineId, patrollineRecord.uniqueTag];
            FMResultSet* set = [db executeQuery:sql];
            if ([set next]) {
                sql = [NSString stringWithFormat:@"UPDATE 'patrolline_record' SET startTime=%f,endTime=%f,count=%ld,result=%ld,status=%d,keepWatchMissionId=%ld,recordId=%ld,recordSequence='%@',executorIds='%@' WHERE patrolLineId=%ld AND uniqueTag like '%@'",patrollineRecord.startTime, patrollineRecord.endTime, (long)patrollineRecord.count, (unsigned long)patrollineRecord.result, 0, (long)patrollineRecord.keepWatchMissionId, (long)patrollineRecord.recordId,patrollineRecord.recordSequence?:@"", patrollineRecord.executorIds,(long)patrollineRecord.patrolLineId, patrollineRecord.uniqueTag];
                executeResult = [db executeUpdate:sql];
            }
            else {
                sql = @"INSERT INTO 'patrolline_record' (patrolLineId, uniqueTag,startTime,endTime,status,count,result,keepWatchMissionId,recordId,recordSequence,executorIds) VALUES (?,?,?,?,?,?,?,?,?,?,?);";
                executeResult = [db executeUpdate:sql, @(patrollineRecord.patrolLineId), patrollineRecord.uniqueTag, @(patrollineRecord.startTime), @(patrollineRecord.endTime), @(0), @(patrollineRecord.count), @(patrollineRecord.result), @(patrollineRecord.keepWatchMissionId), @(patrollineRecord.recordId),patrollineRecord.recordSequence,patrollineRecord.executorIds];
            }
            if (!executeResult) {
                *stop = YES;
                *rollback = YES;
            };
        }];
        [db closeOpenResultSets];
    }];
    return executeResult;
}

- (BOOL)updatePatrollineRecordWithNewWorkData:(NSArray*)patrollineRecords
{
    if (patrollineRecords.count==0) {
        return YES;
    }
    __block BOOL executeResult = YES;
    __block NSString* sql = nil;
    [self.dbQeue inTransaction:^(FMDatabase* db, BOOL* rollback) {
        [patrollineRecords enumerateObjectsUsingBlock:^(YWPatrollineRecord* _Nonnull patrollineRecord, NSUInteger idx, BOOL* _Nonnull stop) {
            sql = [NSString stringWithFormat:@"SELECT  * FROM 'patrolline_record' WHERE patrolLineId=%ld AND uniqueTag like '%%%@%%'",(long)patrollineRecord.patrolLineId, patrollineRecord.uniqueTag];
            FMResultSet* set = [db executeQuery:sql];
            ////网络数据过来的有些字段没有数据就不更新，因为会覆盖掉本地记录，例如count，status
            if ([set next]) {
                sql = [NSString stringWithFormat:@"UPDATE 'patrolline_record' SET startTime=%f,endTime=%f,result=%ld,keepWatchMissionId=%ld,recordId=%ld,recordSequence='%@',executorIds='%@' WHERE patrolLineId =%ld AND uniqueTag like '%@'",patrollineRecord.startTime, patrollineRecord.endTime, (unsigned long)patrollineRecord.result, (long)patrollineRecord.keepWatchMissionId, (long)patrollineRecord.recordId,patrollineRecord.recordSequence,patrollineRecord.executorIds, (long)patrollineRecord.patrolLine.patrolLineId, patrollineRecord.uniqueTag];
                executeResult = [db executeUpdate:sql];
            }
            else {
                ////过来的数据不知道是完成还是没完成，这里都把status设置为完成
                sql = @"INSERT INTO 'patrolline_record' (patrolLineId, uniqueTag ,startTime ,endTime ,status ,count,result,keepWatchMissionId,recordId,recordSequence,executorIds) VALUES (?, ?, ?, ?, ?, ?, ?, ?,?,?,?);";
                executeResult = [db executeUpdate:sql, @(patrollineRecord.patrolLineId), patrollineRecord.uniqueTag, @(patrollineRecord.startTime), @(patrollineRecord.endTime), @(0), @(patrollineRecord.count), @(patrollineRecord.result), @(patrollineRecord.keepWatchMissionId), @(patrollineRecord.recordId),patrollineRecord.recordSequence,patrollineRecord.executorIds];
            }
            if (!executeResult) {
                *stop = YES;
                *rollback = YES;
            };
        }];
        [db closeOpenResultSets];
    }];
    [patrollineRecords enumerateObjectsUsingBlock:^(YWPatrollineRecord* _Nonnull patrollineRecord, NSUInteger idx, BOOL* _Nonnull stop) {
        if (patrollineRecord.patrolLine.patrolLineId>0) {
            [self updatePatrolLineIdAndName:patrollineRecord.patrolLine];
//            [self updatePatrollineWeek:patrollineRecord.patrolLine.weekRels patrolLineId:patrollineRecord.patrolLine.patrolLineId];
//            [self updatePatrollineLocation:patrollineRecord.patrolLine.locationRels patrolLineId:patrollineRecord.patrolLine.patrolLineId];
        }
    }];
    return executeResult;
}
- (YWPatrollineRecord *)latestPatrollineRecordWithUniqueTag:(NSString *)uniqueTag
{
    __block YWPatrollineRecord* patrollineRecord = nil;
    [self.dbQeue inDatabase:^(FMDatabase* db) {
        NSString* sql = @"SELECT * FROM 'patrolline_record' WHERE uniqueTag=? ORDER BY startTime DESC";
        FMResultSet* set = [db executeQuery:sql, uniqueTag];
        if ([set next]) {
            patrollineRecord = [self patrollineRecordWithFMResultSet:set FMDatabase:db];
        }
        [db closeOpenResultSets];
    }];
    return patrollineRecord;
}
- (NSInteger)todayFinishCountWithPatrolLineId:(NSInteger)patrolLineId keepWatchMissionId:(NSInteger)keepWatchMissionId{
    __block NSInteger noReadCount = 0;
    NSTimeInterval nowTime = [[NSDate date] timeIntervalSince1970]*1000;
//    NSString* today = [YWCommonTool showDate:nowTime];
//    today = [today stringByReplacingOccurrencesOfString:@"-" withString:@""];
//    __block NSInteger todayInt = [today integerValue];
    [self.dbQeue inDatabase:^(FMDatabase* db) {
        NSString* countSql = @"SELECT COUNT(*) AS noReadCount FROM 'patrolline_record' WHERE patrolLineId=? AND keepWatchMissionId = ? AND status = ? AND endTime>? AND result!=?";
        FMResultSet* countSet = [db executeQuery:countSql,@(patrolLineId), @(keepWatchMissionId), @(0), @(nowTime),@(YWPatrollineRecordResultSystem)];
        if ([countSet next]) {
            noReadCount = [countSet intForColumn:@"noReadCount"];
        }
        [db closeOpenResultSets];
    }];
    return noReadCount;
}

- (NSArray*)patrollineRecordWithKeepWatchMission:(YWKeepWatchMission*)keepWatchMission page:(NSInteger)page{
    __block NSMutableArray* totalRecords = [NSMutableArray array];
    __block NSInteger showCount = page * 10;
    [self.dbQeue inDatabase:^(FMDatabase* db) {
        NSString* sql = @"SELECT * FROM 'patrolline_record' WHERE patrolLineId=? AND keepWatchMissionId = ? ORDER BY endTime DESC LIMIT ?";//AND status = ?
        FMResultSet* set = [db executeQuery:sql, @(keepWatchMission.patrolLine.patrolLineId), @(keepWatchMission.keepWatchMissionId), @(showCount)];//@(YWPatrollineRecordStatusFinish)
        //按照时间排序后，分时间段显示，构建二维数组
        NSString *lastDate = nil;
        NSMutableArray *array = nil;
        while ([set next]) {
            YWPatrollineRecord* patrollineRecord = [self patrollineRecordWithFMResultSet:set FMDatabase:db];
            NSString *timeDate = [YWCommonTool showChineseFormatterDate:patrollineRecord.endTime/1000];
            if (lastDate==nil || ![timeDate isEqualToString:lastDate]) {
                lastDate = timeDate;
                array = [NSMutableArray array];
            }
            [array addObject:patrollineRecord];
            if (![totalRecords containsObject:array] && array.count) {
                [totalRecords addObject:array];
            }
        }
        [db closeOpenResultSets];
    }];
    return totalRecords;//.count ? totalRecords : nil;
}
- (NSArray*)patrolLineRecordWithPage:(NSInteger)page{
    __block NSInteger showCount = page * 10;
    __block NSMutableArray* records = [NSMutableArray array];
    [self.dbQeue inDatabase:^(FMDatabase* db) {
        NSString *sql = @"SELECT * FROM 'patrolline_record' ORDER BY endTime DESC LIMIT ?";
        FMResultSet* set = [db executeQuery:sql, @(showCount)];
        //按照时间排序后，分时间段显示，构建二维数组
        NSString *lastDate = nil;
        NSMutableArray *array = nil;
        while ([set next]) {
            YWPatrollineRecord* patrollineRecord = [self patrollineRecordWithFMResultSet:set FMDatabase:db];
            NSString *timeDate = [YWCommonTool showChineseFormatterDate:patrollineRecord.endTime/1000];
            if (lastDate==nil || ![timeDate isEqualToString:lastDate]) {
                lastDate = timeDate;
                array = [NSMutableArray array];
            }
            [array addObject:patrollineRecord];
            if (![records containsObject:array] && array.count) {
                [records addObject:array];
            }
        }
        [db closeOpenResultSets];
    }];
    return records;
}
-(BOOL)updateReadStatusWithKeepWatchMissionId:(NSInteger)keepWatchMissionId{
    __block BOOL executeResult = YES;
    [self.dbQeue inDatabase:^(FMDatabase* db) {
        NSString* sql = @"UPDATE 'patrolline_record' SET isRead=? WHERE keepWatchMissionId=?;";
        executeResult = [db executeUpdate:sql, @(YES),@(keepWatchMissionId)];
        [db closeOpenResultSets];
    }];
    return executeResult;
}


- (YWPatrollineRecord*)patrollineRecordWithFMResultSet:(FMResultSet*)set FMDatabase:(FMDatabase*)db
{
    YWPatrollineRecord* patrollineRecord = [[YWPatrollineRecord alloc] init];
    patrollineRecord.patrolLineId = [set intForColumn:@"patrolLineId"];
    NSString* sql = @"SELECT * FROM 'patrolline' WHERE patrolLineId=?";
    FMResultSet* lineSet = [db executeQuery:sql, @(patrollineRecord.patrolLineId)];
    if ([lineSet next]) {
        patrollineRecord.patrolLine = [self patrolLineWithFMResultSet:lineSet FMDatabase:db];
    }
    
    patrollineRecord.uniqueTag = [set stringForColumn:@"uniqueTag"];
    patrollineRecord.startTime = [set longLongIntForColumn:@"startTime"];
    patrollineRecord.endTime = [set longLongIntForColumn:@"endTime"];
    patrollineRecord.count = [set intForColumn:@"count"];
    patrollineRecord.result = [set intForColumn:@"result"];
    patrollineRecord.keepWatchMissionId = [set intForColumn:@"keepWatchMissionId"];
    patrollineRecord.recordId = [set intForColumn:@"recordId"];
    patrollineRecord.recordSequence = [set stringForColumn:@"recordSequence"];
    
    sql = @"SELECT * FROM 'keepwatchmission' WHERE keepWatchMissionId = ?";
    FMResultSet* missionSet = [db executeQuery:sql, @(patrollineRecord.keepWatchMissionId)];
    if ([missionSet next]) {
        YWKeepWatchMission *keepWatchMission = [self keepWatchMissionWithFMResultSet:missionSet FMDatabase:db];
        patrollineRecord.keepWatchMission = keepWatchMission;
    }
    
    patrollineRecord.executorIds = [set stringForColumn:@"executorIds"];
    NSMutableArray *executors = [NSMutableArray array];
    sql = [NSString stringWithFormat:@"SELECT * FROM 'handler' WHERE uniqueId in (%@)",patrollineRecord.executorIds];
    FMResultSet *executorSet = [db executeQuery:sql];
    while ([executorSet next]) {
        YWHandler *handler = [[YWDBTool shareDBTool] handlerWithSet:executorSet];
        [executors addObject:handler];
    }
    patrollineRecord.executors = executors;
    
    return patrollineRecord;
}
- (BOOL)updatePatrolPointRecordWithArray:(NSArray*)patrolPointRecords
{
    if (patrolPointRecords.count==0) {
        return YES;
    }
    __block BOOL executeResult = YES;
    __block NSArray* blockPatrolPointRecords = patrolPointRecords;
    __block NSString* sql = nil;
    [self.dbQeue inTransaction:^(FMDatabase* db, BOOL* rollback) {
        [blockPatrolPointRecords enumerateObjectsUsingBlock:^(YWPatrolPointRecord* _Nonnull patrolPointRecord, NSUInteger idx, BOOL* _Nonnull stop) {
            sql = @"SELECT  * FROM 'patrolpoint_record' WHERE uniqueTag=? AND locationId = ? ";
            FMResultSet* set = [db executeQuery:sql, patrolPointRecord.uniqueTag, @(patrolPointRecord.locationId)];
            if ([set next]) {
                NSTimeInterval patrolTime = [set longLongIntForColumn:@"patrolTime"];//如果一个点多个人做了，保留最新一条的记录
                if (patrolPointRecord.patrolTime>=patrolTime) {
                    if (!(patrolPointRecord.sort==-1 && patrolPointRecord.patrolLineId==0)) {
                        sql = @"UPDATE 'patrolpoint_record' SET status=?,patrolpointRecordId = ?, patrolTime =?, recordNote=? ,sort=? ,uploadStatus =? ,patrolLineId=?,keepWatchMissionId=?,executorId=?,detailUniqueTag=? WHERE uniqueTag =? AND locationId =? ;";
                        executeResult = [db executeUpdate:sql, @(patrolPointRecord.status), patrolPointRecord.patrolpointRecordId, @(patrolPointRecord.patrolTime), patrolPointRecord.note, @(patrolPointRecord.sort), @(patrolPointRecord.uploadStatus), @(patrolPointRecord.patrolLineId), @(patrolPointRecord.keepWatchMissionId),@(patrolPointRecord.executor.uniqueId),patrolPointRecord.detailUniqueTag, patrolPointRecord.uniqueTag, @(patrolPointRecord.locationId)];
                    }else{
                        //不更新sort patrolLineId，keepWatchMissionId
                        sql = @"UPDATE 'patrolpoint_record' SET status=?,patrolpointRecordId =?, patrolTime =?, recordNote=? ,uploadStatus =?,executorId=?,detailUniqueTag=? WHERE uniqueTag =? AND locationId =? ;";
                        executeResult = [db executeUpdate:sql, @(patrolPointRecord.status), patrolPointRecord.patrolpointRecordId, @(patrolPointRecord.patrolTime), patrolPointRecord.note, @(patrolPointRecord.uploadStatus),@(patrolPointRecord.executor.uniqueId),patrolPointRecord.detailUniqueTag, patrolPointRecord.uniqueTag, @(patrolPointRecord.locationId)];
                    }
                }
            }
            else {
                sql = @"INSERT INTO 'patrolpoint_record' (patrolpointRecordId ,uniqueTag ,status,locationId, patrolTime ,recordNote,sort,uploadStatus,patrolLineId,keepWatchMissionId,executorId,detailUniqueTag) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);";
                executeResult = [db executeUpdate:sql, patrolPointRecord.patrolpointRecordId, patrolPointRecord.uniqueTag, @(patrolPointRecord.status), @(patrolPointRecord.locationId), @(patrolPointRecord.patrolTime), patrolPointRecord.note, @(patrolPointRecord.sort), @(patrolPointRecord.uploadStatus), @(patrolPointRecord.patrolLineId), @(patrolPointRecord.keepWatchMissionId),@(patrolPointRecord.executor.uniqueId),patrolPointRecord.detailUniqueTag];
            }
            if (!executeResult) {
                *stop = YES;
                *rollback = YES;
            };
        }];
        [db closeOpenResultSets];
    }];
    return executeResult;
}
- (NSArray*)patrolPointRecordWithUniqueTag:(NSString*)uniqueTag;
{
    __block NSMutableArray* records = [NSMutableArray array];
    [self.dbQeue inDatabase:^(FMDatabase* db) {
        NSString* sql = @"SELECT * FROM 'patrolpoint_record' WHERE uniqueTag=? ORDER BY sort";
        FMResultSet* set = [db executeQuery:sql, uniqueTag];
        while ([set next]) {
            YWPatrolPointRecord* record = [self patrolPointRecordWithFMResultSet:set FMDatabase:db];
            [records addObject:record];
        }
        [db closeOpenResultSets];
    }];
    return records;
}

- (NSInteger)countOfPatrolPointRecordWithUniqueTag:(NSString*)uniqueTag{
    __block NSInteger noReadCount = 0;
    [self.dbQeue inDatabase:^(FMDatabase* db) {
        NSString* countSql = @"SELECT COUNT(*) AS recordCount FROM 'patrolpoint_record' WHERE uniqueTag=?";
        FMResultSet* countSet = [db executeQuery:countSql,uniqueTag];
        if ([countSet next]) {
            noReadCount = [countSet intForColumn:@"recordCount"];
        }
        [db closeOpenResultSets];
    }];
    return noReadCount;
}

-(NSArray *)patrolPointRecordWithLocationId:(NSInteger)locationId{
    __block NSMutableArray* records = [NSMutableArray array];
    [self.dbQeue inDatabase:^(FMDatabase* db) {
        NSString* sql = @"SELECT * FROM 'patrolpoint_record' WHERE locationId=? ORDER BY patrolTime DESC";
        FMResultSet* set = [db executeQuery:sql, @(locationId)];
        while ([set next]) {
            YWPatrolPointRecord* record = [self patrolPointRecordWithFMResultSet:set FMDatabase:db];
            [records addObject:record];
        }
        [db closeOpenResultSets];
    }];
    return records;
}

- (BOOL)updatePatrolPointRecordUploadStatus:(NSInteger)uploadStatus pointRecordId:(NSString*)recordId
{
    __block BOOL executeResult = YES;
    [self.dbQeue inDatabase:^(FMDatabase* db) {
        NSString* sql = @"UPDATE 'patrolpoint_record' SET uploadStatus=? WHERE patrolpointRecordId=?;";
        executeResult = [db executeUpdate:sql, @(uploadStatus), recordId];
        [db closeOpenResultSets];
    }];
    return executeResult;
}
- (NSArray*)patrolPointRecordsWithUploadStatusFail
{
    __block NSMutableArray* records = [NSMutableArray array];
    __block NSMutableArray *uniqueTagArray = [NSMutableArray array];
    [self.dbQeue inDatabase:^(FMDatabase* db) {
        NSString *selectSql = @"SELECT distinct uniqueTag FROM 'patrolpoint_record' WHERE uploadStatus=? or uploadStatus=?";
        FMResultSet* resuleSet = [db executeQuery:selectSql, @(YWPatrolPointRecordUploadStatusUnFinished),@(YWPatrolPointRecordUploadStatusUploading)];
        while ([resuleSet next]) {
            NSString *uniqueTag = [resuleSet stringForColumn:@"uniqueTag"];
            if (![uniqueTagArray containsObject:uniqueTag]) {
                [uniqueTagArray addObject:uniqueTag];
            }
        }
        [uniqueTagArray enumerateObjectsUsingBlock:^(NSString*  _Nonnull uniqueTag, NSUInteger idx, BOOL * _Nonnull stop) {
            NSMutableArray *tempArray = [NSMutableArray array];
            NSString *sql = @"SELECT * FROM 'patrolpoint_record' WHERE uniqueTag=? AND (uploadStatus=? or uploadStatus=?) AND locationId>0";
            FMResultSet* set = [db executeQuery:sql, uniqueTag,@(YWPatrolPointRecordUploadStatusUnFinished),@(YWPatrolPointRecordUploadStatusUploading)];
            while ([set next]) {
                YWPatrolPointRecord* record = [self patrolPointRecordWithFMResultSet:set FMDatabase:db];
                [tempArray addObject:record];
                if (tempArray.count == 20) {//限制一次提交的点不超过50个
                    [records addObject:tempArray];
                    tempArray = [NSMutableArray array];
                }
            }
            if (tempArray.count>0 && ![records containsObject:tempArray]) {
                [records addObject:tempArray];
            }
            //限制一次离线取出的数据不超过600条，防止内存太高
            if (records.count == 30) {
                *stop = YES;
            }
        }];
        [db closeOpenResultSets];
    }];
    return records;
}

- (NSArray *)uploadFailedPatrolPointRecords{
    __block NSMutableArray* records = [NSMutableArray array];
    [self.dbQeue inDatabase:^(FMDatabase* db) {
        NSString *sql = @"SELECT * FROM 'patrolpoint_record' WHERE uploadStatus=? or uploadStatus=? order by uniqueTag,patrolTime desc";
        FMResultSet* set = [db executeQuery:sql, @(YWPatrolPointRecordUploadStatusUnFinished),@(YWPatrolPointRecordUploadStatusUploading)];
        while ([set next]) {
            YWPatrolPointRecord* record = [self patrolPointRecordWithFMResultSet:set FMDatabase:db];
            [records addObject:record];
        }
        [db closeOpenResultSets];
    }];
    return records;
}

- (YWPatrolPointRecord*)patrolPointRecordWithFMResultSet:(FMResultSet*)set FMDatabase:(FMDatabase*)db
{
    YWPatrolPointRecord* patrolPointRecord = [[YWPatrolPointRecord alloc] init];
    patrolPointRecord.patrolpointRecordId = [set stringForColumn:@"patrolpointRecordId"];
    patrolPointRecord.uniqueTag = [set stringForColumn:@"uniqueTag"];
    patrolPointRecord.status = [set intForColumn:@"status"];
    patrolPointRecord.locationId = [set intForColumn:@"locationId"];
    patrolPointRecord.patrolTime = [set longLongIntForColumn:@"patrolTime"];
    patrolPointRecord.note = [set stringForColumn:@"recordNote"];
    patrolPointRecord.sort = [set intForColumn:@"sort"];
    patrolPointRecord.uploadStatus = [set intForColumn:@"uploadStatus"];
    patrolPointRecord.patrolLineId = [set intForColumn:@"patrolLineId"];
    patrolPointRecord.keepWatchMissionId = [set intForColumn:@"keepWatchMissionId"];
    patrolPointRecord.detailUniqueTag = [set stringForColumn:@"detailUniqueTag"];
    NSInteger executorId = [set intForColumn:@"executorId"];
    NSString* executorsql = @"SELECT * FROM 'handler' WHERE uniqueId = ?;";
    FMResultSet* newSet = [db executeQuery:executorsql, @(executorId)];
    if ([newSet next]) {
        patrolPointRecord.executor = [[YWDBTool shareDBTool] handlerWithSet:newSet];
    }
    return patrolPointRecord;
}

- (NSArray*)allPatrolLocationWithPatrolLineId:(NSInteger)patrolLineId
{
    __block NSMutableArray* locations = [NSMutableArray array];
    [self.dbQeue inDatabase:^(FMDatabase* db) {
        NSString* sql = @"SELECT * FROM 'patrolline_location_rel' WHERE patrolLineId=? ORDER BY sort";
        FMResultSet* set = [db executeQuery:sql, @(patrolLineId)];
        while ([set next]) {
            YWPatrolLineLocationRel* locationRel = [[YWPatrolLineLocationRel alloc] init];
            locationRel.patrolLineId = [set intForColumn:@"patrolLineId"];
            locationRel.sort = [set intForColumn:@"sort"];
            locationRel.locationId = [set intForColumn:@"locationId"];
            locationRel.note = [set stringForColumn:@"note"];
            [locations addObject:locationRel];
            
            NSString* locationSql = @"SELECT * FROM 'location' WHERE locationId = ?;";
            FMResultSet* locationSet = [db executeQuery:locationSql, @(locationRel.locationId)];
            if ([locationSet next]) {
                locationRel.location = [[YWDBTool shareDBTool] locationWithFMResultSet:locationSet];
            }
        }
        [db closeOpenResultSets];
    }];
//    for (YWPatrolLineLocationRel* locationRel in locations) {
//        locationRel.location = [[YWDBTool shareDBTool] locationWithId:locationRel.locationId];
//    }
    return locations;
}

- (BOOL)isExistLocationWithPatrolLineId:(NSInteger)patrolLineId{
    __block BOOL ret = NO;
    [self.dbQeue inDatabase:^(FMDatabase* db) {
        NSString* selectSQL = @"SELECT * FROM 'patrolline_location_rel' WHERE patrolLineId=? LIMIT 1";
        FMResultSet* set = [db executeQuery:selectSQL, @(patrolLineId)];
        if ([set next]) {
            ret = YES;
        }
        [db closeOpenResultSets];
    }];
    return ret;
}

- (NSArray*)patrolLocationNoteWithLocationId:(NSInteger)locationId lineIds:(NSArray*)patrolLineIdArray
{
    __block NSMutableArray* locations = [NSMutableArray array];
    [self.dbQeue inDatabase:^(FMDatabase* db) {
        [patrolLineIdArray enumerateObjectsUsingBlock:^(NSString*  _Nonnull patrolLineId, NSUInteger idx, BOOL * _Nonnull stop) {
            NSInteger lineId = [patrolLineId integerValue];
            NSString* sql = @"SELECT * FROM 'patrolline_location_rel' WHERE locationId=? AND patrolLineId=? ORDER BY patrolLineId";
            FMResultSet* set = [db executeQuery:sql, @(locationId),@(lineId)];
            while ([set next]) {
                NSString* note = [set stringForColumn:@"note"];
                if (note.length) {
                    YWPatrolLineLocationRel* locationRel = [[YWPatrolLineLocationRel alloc] init];
                    locationRel.patrolLineId = [set intForColumn:@"patrolLineId"];
                    locationRel.sort = [set intForColumn:@"sort"];
                    locationRel.locationId = [set intForColumn:@"locationId"];
                    locationRel.note = note;
                    [locations addObject:locationRel];
                }
            }
        }];
        
        [db closeOpenResultSets];
    }];
    //    for (YWPatrolLineLocationRel* locationRel in locations) {
    //        locationRel.location = [self locationWithId:locationRel.locationId];
    //    }
    return locations;
}
- (YWPatrolLine*)patrolLineWithPatrolLineId:(NSInteger)patrolLineId
{
    __block YWPatrolLine* patrolLine = nil;
    [self.dbQeue inDatabase:^(FMDatabase* db) {
        NSString* sql = @"SELECT * FROM 'patrolline' WHERE patrolLineId=?";
        FMResultSet* set = [db executeQuery:sql, @(patrolLineId)];
        if ([set next]) {
            patrolLine = [self patrolLineWithFMResultSet:set FMDatabase:db];
        }
        [db closeOpenResultSets];
    }];
    
    return patrolLine;
}
- (NSInteger)sortWithLocationId:(NSInteger)locationId patrolLineId:(NSInteger)patrolLineId
{
    __block NSInteger sort;
    [self.dbQeue inDatabase:^(FMDatabase* db) {
        NSString* sql = @"SELECT * FROM 'patrolline_location_rel' WHERE locationId=? AND patrolLineId=?";
        FMResultSet* set = [db executeQuery:sql, @(locationId), @(patrolLineId)];
        if ([set next]) {
            sort = [set intForColumn:@"sort"];
        }
        [db closeOpenResultSets];
    }];
    return sort;
}
- (NSArray*)keepWatchMissionWithLocationId:(NSInteger)locationId
{
    __block NSMutableArray* keepWatchMissionArray = [NSMutableArray array];
    __block NSMutableArray* patrolLineIdArr = [NSMutableArray array];
    __block NSMutableArray* keepWatchMissionIdArr = [NSMutableArray array];
    [self.dbQeue inDatabase:^(FMDatabase* db) {
        NSString* sql = @"SELECT * FROM 'patrolline_location_rel' WHERE locationId=?";
        FMResultSet* set = [db executeQuery:sql, @(locationId)];
        while ([set next]) {
            NSInteger patrolLineId = [set intForColumn:@"patrolLineId"];
            [patrolLineIdArr addObject:[NSString stringWithFormat:@"%ld", (long)patrolLineId]];
        }
        for (NSString* patrolLineId in patrolLineIdArr) {
            NSString* sql = @"select distinct keepWatchMissionId FROM 'keepwatchmission' where keepWatchMissionId>0 AND patrolLineId=? AND status=0";
            FMResultSet* set = [db executeQuery:sql, @([patrolLineId integerValue])];
            while ([set next]) {
                NSInteger keepWatchMissionId = [set intForColumn:@"keepWatchMissionId"];
                [keepWatchMissionIdArr addObject:@(keepWatchMissionId)];
            }
        }
        [db closeOpenResultSets];
    }];
    NSArray* allMissions = [self allKeepWatchMissions];
    [allMissions enumerateObjectsUsingBlock:^(YWKeepWatchMission* _Nonnull keepWatchMission, NSUInteger idx, BOOL* _Nonnull stop) {
        if ([keepWatchMissionIdArr containsObject:@(keepWatchMission.keepWatchMissionId)] && [YWCommonTool containSelfInHandlers:keepWatchMission.executors]) {
            [keepWatchMissionArray addObject:keepWatchMission];
        }
    }];
    
    return keepWatchMissionArray;
}
@end
